﻿using System;
using SmartSiteManagementSystem.IRepository.WorkTask;
using SmartSiteManagementSystem.Models.WorkTask;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;

namespace SmartSiteManagementSystem.Repository.WorkTask
{
    public class TaskInfoRepository : ITaskInfoRepository
    {
        #region dataTable
        /*
drop table if exists `task`;
create table `task`( 
	`task_id` int(11) not null AUTO_INCREMENT primary key, 
    `task_name` nvarchar(250) NOT NULL,
    `batch_id` int NOT NULL,
    `taskset_id` int NOT NULL,
    `demand_material_id` int NOT NULL,
	`demandingplace_position_x` double(10,2)  NOT NULL default 0,
	`demandingplace_position_y` double(10,2)  NOT NULL default 0,
	`demandingplace_position_z` double(10,2)  NOT NULL default 0,
    `demandingplace_id` int NOT NULL,
    `material_arrival_time` datetime not null default now(),
    `weight` int  NOT NULL default 0,
    `sla` int  NOT NULL default 0,
    `descript` text null,
    `is_delete` bit NOT NULL default 0,
	`create_time` datetime not null default now(),
	`update_time` datetime not null default now()
) default charset=utf8;
         */
        #endregion
        //private IDbConnection _readConn;
       // private IDbConnection _writeConn;

        public bool Delete(Hashtable param)
        {
            bool back = false;
            if (param == null)
                param = new Hashtable();
            String querySql = @"delete from task";
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where ");
                    int parCount = 0;
                    if (param.ContainsKey("task_id"))
                    {
                        pars.Add("task_id", param["task_id"]);

                        whereCondition.Append(" task_id =@task_id");
                        parCount++;
                    }
                }
                string execSql = querySql + whereCondition.ToString();
                int backInt = _writeConn.Execute(execSql, pars);
                if (backInt > 0)
                {
                    back = true;
                }
            }
            return back;
        }

        public TaskInfo Get(Hashtable param)
        {
            TaskInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT task_id as TaskId, task_name as TaskName,batch_id as BatchId,taskset_id as TaskSetId,demand_material_id as DemandMaterialId,demandingplace_position_x as DemandingPlacePositionX,demandingplace_position_y as DemandingPlacePositionY,demandingplace_position_z as DemandingPlacePositionZ,demandingplace_id as DemandingPlaceId,material_arrival_time as MaterialArrivalTime,weight as Weight,sla as SLA,descript as Descript,is_delete as IsDelete,create_time as CreateTime,update_time as UpdateTime
 FROM task";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("task_id"))
                    {
                        pars.Add("task_id", param["task_id"]);
                        whereCondition.Append(" and task_id =@task_id");
                    }
                    if (param.ContainsKey("batch_id"))
                    {
                        pars.Add("batch_id", param["batch_id"]);
                        whereCondition.Append(" and batch_id =@batch_id");
                    }

                    if (param.ContainsKey("task_name"))
                    {
                        pars.Add("task_name", param["task_name"]);
                        whereCondition.Append(" and task_name =@task_name");
                    }
                    if (param.ContainsKey("taskset_id"))
                    {
                        pars.Add("taskset_id", param["taskset_id"]);
                        whereCondition.Append(" and taskset_id =@taskset_id");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }


                    
                }
                whereCondition.Append(" limit 1");
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.QueryFirstOrDefault<TaskInfo>(execSql, pars);
            }
            return back;
        }

        public int GetCount(Hashtable param)
        {
            int back = 0;
            if (param == null)
                param = new Hashtable();
            String querySql = "select count(task_id) from task";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("task_id"))
                    {
                        pars.Add("task_id", param["task_id"]);
                        whereCondition.Append(" and task_id =@task_id");
                    }
                    if (param.ContainsKey("batch_id"))
                    {
                        pars.Add("batch_id", param["batch_id"]);
                        whereCondition.Append(" and batch_id =@batch_id");
                    }

                    if (param.ContainsKey("task_name"))
                    {
                        pars.Add("task_name", param["task_name"]);
                        whereCondition.Append(" and task_name =@task_name");
                    }
                    if (param.ContainsKey("taskset_id"))
                    {
                        pars.Add("taskset_id", param["taskset_id"]);
                        whereCondition.Append(" and taskset_id =@taskset_id");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }
                }

                string execSql = querySql + whereCondition.ToString();
                #endregion
                back = _readConn.QueryFirstOrDefault<int>(execSql, pars);
            }
            return back;
        }

        public IList<TaskInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {

            IList<TaskInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT task_id as TaskId, task_name as TaskName,batch_id as BatchId,taskset_id as TaskSetId,demand_material_id as DemandMaterialId,demandingplace_position_x as DemandingPlacePositionX,demandingplace_position_y as DemandingPlacePositionY,demandingplace_position_z as DemandingPlacePositionZ,demandingplace_id as DemandingPlaceId,material_arrival_time as MaterialArrivalTime,weight as Weight,sla as SLA,descript as Descript,is_delete as IsDelete,create_time as CreateTime,update_time as UpdateTime
 FROM task";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("task_id"))
                    {
                        pars.Add("task_id", param["task_id"]);
                        whereCondition.Append(" and task_id =@task_id");
                    }
                    if (param.ContainsKey("batch_id"))
                    {
                        pars.Add("batch_id", param["batch_id"]);
                        whereCondition.Append(" and batch_id =@batch_id");
                    }

                    if (param.ContainsKey("task_name"))
                    {
                        pars.Add("task_name", param["task_name"]);
                        whereCondition.Append(" and task_name =@task_name");
                    }
                    if (param.ContainsKey("taskset_id"))
                    {
                        pars.Add("taskset_id", param["taskset_id"]);
                        whereCondition.Append(" and taskset_id =@taskset_id");
                    }
                    if (param.ContainsKey("is_delete"))
                    {
                        pars.Add("is_delete", param["is_delete"]);
                        whereCondition.Append(" and is_delete =@is_delete");
                    }
                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by task_id desc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<TaskInfo>(execSql, pars).AsList<TaskInfo>();
            }
            return back;
        }

        public int Insert(TaskInfo obj)
        {
            int back = 0;
            #region sql
            string sql = @"INSERT INTO task
(
    task_name,
    batch_id,
    taskset_id,
    demand_material_id,
	demandingplace_position_x,
	demandingplace_position_y,
	demandingplace_position_z,
    demandingplace_id,
    material_arrival_time,
    weight,
    sla,
    descript,
    is_delete,
	create_time,
	update_time
)
VALUES
(   @task_name,
    @batch_id,
    @taskset_id,
    @demand_material_id,
	@demandingplace_position_x,
	@demandingplace_position_y,
	@demandingplace_position_z,
    @demandingplace_id,
    @material_arrival_time,
    @weight,
    @sla,
    @descript,
    @is_delete,
	@create_time,
	@update_time);select last_insert_id();";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                back = _writeConn.QueryFirst<int>(sql, new
                {
                    task_name=obj.TaskName,
                    batch_id = obj.BatchId,
                    taskset_id = obj.TaskSetId,
                    demand_material_id = obj.DemandMaterialId,
                    demandingplace_position_x = obj.DemandingPlacePositionX,
                    demandingplace_position_y = obj.DemandingPlacePositionY,
                    demandingplace_position_z = obj.DemandingPlacePositionZ,
                    demandingplace_id = obj.DemandingPlaceId,
                    material_arrival_time = obj.MaterialArrivalTime,
                    weight = obj.Weight,
                    sla = obj.SLA,
                    descript = obj.Descript,
                    is_delete = obj.IsDelete,
                    create_time = obj.CreateTime,
                    update_time = obj.UpdateTime
                });
            }
            return back;
        }

        public bool Update(TaskInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @"update task
set
task_name=@task_name,
batch_id=@batch_id,
taskset_id=@taskset_id,
demand_material_id=@demand_material_id,
demandingplace_position_x=@demandingplace_position_x,
demandingplace_position_y=@demandingplace_position_y,
demandingplace_position_z=@demandingplace_position_z,
demandingplace_id=@demandingplace_id,
material_arrival_time=@material_arrival_time,
weight=@weight,
sla=@sla,
descript=@descript,
is_delete=@is_delete,
create_time=@create_time,
update_time=@update_time
WHERE task_id =@task_id;";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                int backint = _writeConn.Execute(sql, new
                {
                    task_id = obj.TaskId,
                    task_name = obj.TaskName,
                    batch_id = obj.BatchId,
                    taskset_id = obj.TaskSetId,
                    demand_material_id = obj.DemandMaterialId,
                    demandingplace_position_x = obj.DemandingPlacePositionX,
                    demandingplace_position_y = obj.DemandingPlacePositionY,
                    demandingplace_position_z = obj.DemandingPlacePositionZ,
                    demandingplace_id = obj.DemandingPlaceId,
                    material_arrival_time = obj.MaterialArrivalTime,
                    weight = obj.Weight,
                    sla = obj.SLA,
                    descript = obj.Descript,
                    is_delete = obj.IsDelete,
                    create_time = obj.CreateTime,
                    update_time = obj.UpdateTime
                });
                if (backint > 0)
                {
                    back = true;
                }
            }
            return back;
        }
    }
}

